package com.css.fxfzxczh.modules.aftershockSequence.service.impl;


import com.css.fxfzxczh.modules.aftershockSequence.service.SetEarthquakeMapService;
import com.css.fxfzxczh.util.PlatformObjectUtils;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @author y1fan
 * @description
 */
@Service
public class SetEarthquakeMapServiceImpl implements SetEarthquakeMapService {
    /**
     * 数据源
     * 10.19.41.111
     */
    @Value("${spring.datasource.url}")
    private String url = "";
    @Value("${spring.datasource.username}")
    private String name = "";
    @Value("${spring.datasource.password}")
    private String pwd = "";

    //根据经纬度查询行政区划--省份code和名称
    @Override
    public List<String> queryProvinceByLonLat(String lon, String lat) {
        List<String> resultList = new ArrayList<String>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            if (!PlatformObjectUtils.isEmpty(lon) && !PlatformObjectUtils.isEmpty(lat)) {
                conn = getConnection();
                //查询行政区划--省
                String sql = "SELECT code,name FROM district_boundary_province t " +
                        "WHERE ST_Within(ST_GeometryFromText('POINT (" + lon + " " + lat + ")',4490),t.geom) ";
                ps = conn.prepareStatement(sql);
                rs = ps.executeQuery();
                while (rs.next()) {
                    String code = rs.getString("code");//行政区划编码
                    resultList.add(code);
                    String name = rs.getString("name");//行政区划名称
                    resultList.add(name);
                }
            }
        } catch (Exception e2) {
            e2.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
            try {
                if (ps != null) {
                    // 关闭连接
                    ps.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }

        return resultList;
    }

    //根据经纬度查询行政区划--市code和名称
    @Override
    public List<String> queryCityByLonLat(String lon, String lat, String provinceCode) {
        List<String> resultList = new ArrayList<String>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            if (!PlatformObjectUtils.isEmpty(lon) && !PlatformObjectUtils.isEmpty(lat)) {
                conn = getConnection();
                //查询行政区划--市
                String sql = "SELECT code,name FROM district_boundary_city t WHERE ";
                if (!PlatformObjectUtils.isEmpty(provinceCode)) {//省code
                    String tempCode = provinceCode.substring(0, 2);
                    sql += "t.code like '" + tempCode + "%' and ";
                }
                sql += "ST_Within(ST_GeometryFromText('POINT (" + lon + " " + lat + ")',4490),t.geom) ";

                ps = conn.prepareStatement(sql);
                rs = ps.executeQuery();
                while (rs.next()) {
                    String code = rs.getString("code");//行政区划编码
                    resultList.add(code);
                    String name = rs.getString("name");//行政区划名称
                    resultList.add(name);
                }
            }
        } catch (Exception e2) {
            e2.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
            try {
                if (ps != null) {
                    // 关闭连接
                    ps.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }

        return resultList;
    }

    //根据经纬度查询行政区划--区县code和名称
    @Override
    public List<String> queryCountyByLonLat(String lon, String lat, String cityCode) {
        List<String> resultList = new ArrayList<String>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            if (!PlatformObjectUtils.isEmpty(lon) && !PlatformObjectUtils.isEmpty(lat)) {
                conn = getConnection();
                //查询行政区划--区县
                String sql = "SELECT code,name FROM district_boundary_county t WHERE ";
                if (!PlatformObjectUtils.isEmpty(cityCode)) {//市code
                    String tempCode = cityCode.substring(0, 4);
                    sql += "t.code like '" + tempCode + "%' and ";
                }
                sql += "ST_Within(ST_GeometryFromText('POINT (" + lon + " " + lat + ")',4490),t.geom) ";

                ps = conn.prepareStatement(sql);
                rs = ps.executeQuery();
                while (rs.next()) {
                    String code = rs.getString("code");//行政区划编码
                    resultList.add(code);
                    String name = rs.getString("name");//行政区划名称
                    resultList.add(name);
                }
            }
        } catch (Exception e2) {
            e2.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
            try {
                if (ps != null) {
                    // 关闭连接
                    ps.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }

        return resultList;
    }

    /**
     * 获取数据库连接
     * SQL语句
     */
    private Connection getConnection() throws Exception {
        //Class.forName("com.mysql.cj.jdbc.Driver");
        Class.forName("org.postgresql.Driver");
        Connection conn = DriverManager.getConnection(url, name, pwd);
        return conn;
    }
}
